--    Author    : ChenErHao
--    Name      : DM.T_MTH_DEP_STRUCTURE.HQL
--    Functions : 结构-存款机构分析
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-01-22  ChenErHao       1.CREATE THE PROCEDURE
--

INSERT OVERWRITE TABLE DM.T_MTH_DEP_STRUCTURE PARTITION (FISCAL_MTH_DIMNSN_ID = '#V_DATA_MONTH#') 
SELECT   
    T1.AREA_NO_ID,
    T1.CUSTOMER_TYPE,
    T1.PRODUCT_CATEGORY,
    T1.DEP_PERIOD,
    nvl(sum(case when FISCAL_MTH_DIMNSN_ID='#V_DATA_DATE#' then T1.DEP_BALANCE end),0) DEP_BALANCE,
    nvl(sum(case when FISCAL_MTH_DIMNSN_ID='#V_LAST_1M_END_DATE#' then T1.DEP_BALANCE end),0) LAST_MTH_DEP_BALANCE,
    nvl(sum(case when FISCAL_MTH_DIMNSN_ID='#V_LAST_12M_END_DATE#' then T1.DEP_BALANCE end),0) LAST_YEAR_DEP_BALANCE,
    nvl(sum(case when FISCAL_MTH_DIMNSN_ID='#V_DATA_DATE#' then T1.DEP_BALANCE end),0) YEAR_END_DEP_BALANCE,
    nvl(sum(case when FISCAL_MTH_DIMNSN_ID='#V_LAST_1Y_END_DAY#' then T1.DEP_BALANCE end),0) LAST_YEAR_END_DEP_BALANCE,
    nvl(sum(case when FISCAL_MTH_DIMNSN_ID='#V_DATA_DATE#' then T1.WEIGHT_AMT end),0) WEIGHT_AMT
FROM
    (SELECT
            DATA_DATE AS FISCAL_MTH_DIMNSN_ID
            ,BELONG_AREA_CODE AS AREA_NO_ID
            ,CASE WHEN C.depb_category_id_1 IS NULL THEN '9999'
                 WHEN C.depb_category_id_1='' THEN '9999'
                 ELSE C.depb_category_id_1 
             END AS PRODUCT_CATEGORY  --存款产品类别
            ,CUSTOMER_TYPE AS CUSTOMER_TYPE
            ,LOAN_PERIOD_TYPE_ID AS DEP_PERIOD
            ,SUM(DEP_BALANCE) AS DEP_BALANCE
            ,SUM(LOAN_OCCURRENCE_LEVEL) AS WEIGHT_AMT
        FROM
            EDW.DEPB A
        LEFT JOIN DMCODE.T_DEPB_CATEGORY C ON CASE WHEN a.PRODUCT_CATEGORY IS NULL THEN CONCAT('pro_hive',RAND()) ELSE A.PRODUCT_CATEGORY END = C.DEPB_CATEGORY_ID
        WHERE
            DATA_DATE in ('#V_DATA_DATE#','#V_LAST_1M_END_DATE#','#V_LAST_12M_END_DATE#','#V_LAST_1Y_END_DAY#') 
            GROUP BY 
                 DATA_DATE,
                 A.BELONG_AREA_CODE,
                 C.depb_category_id_1,
                 CUSTOMER_TYPE,
                 LOAN_PERIOD_TYPE_ID ) T1
GROUP BY
    T1.AREA_NO_ID,
    T1.CUSTOMER_TYPE,
    T1.PRODUCT_CATEGORY,
    T1.DEP_PERIOD 
;